DROP SEQUENCE seq_department;
DROP SEQUENCE seq_doctor;
DROP SEQUENCE seq_patient;
DROP SEQUENCE seq_medicine;
DROP SEQUENCE seq_rating_record;
DROP SEQUENCE seq_rating_type;
DROP SEQUENCE seq_treatment_record;



CREATE SEQUENCE seq_department MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
CREATE SEQUENCE seq_doctor MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
CREATE SEQUENCE seq_patient MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
CREATE SEQUENCE seq_medicine MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
CREATE SEQUENCE seq_rating_record MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
CREATE SEQUENCE seq_rating_type MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;
CREATE SEQUENCE seq_treatment_record MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;


DROP TABLE S_DEPARTMENT;
CREATE TABLE S_DEPARTMENT (
  SEQ NUMBER(8, 0) NOT NULL,
  NAME VARCHAR2(50),
  DESCRIPTION VARCHAR2(300),
  BUILD_DATE DATE
);
CREATE UNIQUE INDEX PK_S_DEPARTMENT ON S_DEPARTMENT (SEQ);



DROP TABLE S_USERS;
CREATE TABLE S_USERS (
  SEQ NUMBER(8, 0) NOT NULL,
  ID VARCHAR2(50),
  PASSWORD VARCHAR2(50),
  NAME VARCHAR2(50),  
  GENDER NUMBER(1,0),
  DEPARTMENT_ID NUMBER(8, 0),
  DEGREE VARCHAR2(50),
  UNIVERSITY VARCHAR2(50),
  WORK_TYPE NUMBER(2, 0),
  WORK_DOMAIN VARCHAR2(100),
  INTRODUCTION VARCHAR2(300),
  BIRTH_DATE DATE,
  JOIN_HOSPITAL_DATE DATE,
  JOIN_WORK_DATE DATE
);

CREATE UNIQUE INDEX PK_S_DOCTOR ON S_USERS (SEQ);
CREATE UNIQUE INDEX PK_S_DOCTOR_2 ON S_USERS (ID);

DROP TABLE S_PATIENT;
CREATE TABLE S_PATIENT (
  SEQ NUMBER(8, 0) NOT NULL,
  NAME VARCHAR2(100),
  GENDER NUMBER(1,0),
  BIRTH_DATE DATE,
  PHONE_NUMBER VARCHAR2(50)
);
CREATE UNIQUE INDEX PK_S_PATIENT ON S_PATIENT (SEQ);

DROP TABLE S_MEDICINE;
CREATE TABLE S_MEDICINE (
	key_1 VARCHAR(50),
	name VARCHAR(100),
	gen_number VARCHAR(100),
	note VARCHAR(200),
	price float,
	quantity NUMBER(8, 0),
	ordered_quantity NUMBER(8, 0),
	soldQuantity NUMBER(8, 0)
);

DROP TABLE S_CHARGE;
CREATE TABLE S_CHARGE (
	key_1 VARCHAR(50),
	doctor_seq NUMBER(8, 0),
	patient_seq NUMBER(8, 0),
	fee float,
	gen_date Date, 
	pay_date Date,
	status NUMBER(8, 0),
	type_1 NUMBER(8, 0),
	seq NUMBER(8, 0),
	description VARCHAR(50)
);



CREATE TABLE S_news (
	key VARCHAR(50),
	title VARCHAR(100),
	content VARCHAR(2000),
	date DATE
);


DROP TABLE S_TREATMENT_RECORD;
CREATE TABLE S_TREATMENT_RECORD (
  SEQ NUMBER(10, 0) NOT NULL,
  PATIENT_ID NUMBER(8, 0),
  DOCTOR_ID NUMBER(8, 0),
  GIVE_MEDICINE_DOCTOR_ID NUMBER(8, 0),
  DEPARTMENT_ID NUMBER(8, 0) NOT NULL,
  MEDICNE_IDS_AND_NUMS VARCHAR(200),
  TOTAL_FEE NUMBER(10, 2),
  DOCTOR_COMMENT VARCHAR2(300),
  SYMPTOM VARCHAR2(300),
  TREAT_DATE DATE,
  status NUMBER(3, 0)
);
CREATE UNIQUE INDEX PK_S_TREATMENT_RECORD ON S_TREATMENT_RECORD (SEQ);

DROP TABLE S_RATING_TYPE;
CREATE TABLE S_RATING_TYPE (
  SEQ NUMBER(5, 0) NOT NULL,
  NAME VARCHAR2(10),
  RATING_LEVEL_BY_TAGS VARCHAR2(50),
  RATING_MAXSIMUN_SCORE NUMBER(10,2),
  DELETE_FLAG NUMBER(1, 0)
);
CREATE  INDEX PK_S_RATING_TYPE ON S_RATING_TYPE (SEQ);

DROP TABLE S_RATING_RECORD;
CREATE TABLE S_RATING_RECORD (
  SEQ NUMBER(5, 0) NOT NULL,
  FROM_DOCTOR_ID NUMBER(8, 0),
  TO_DOCTOR_ID NUMBER(8, 0),
  PATIENT_ID NUMBER(8, 0),
  RATING_SEQ NUMBER(5, 0),
  RATING_RECORD VARCHAR2(50)
);
CREATE  INDEX PK_S_RATING_RECORD ON S_RATING_RECORD (SEQ);

DROP TABLE S_USERROLE;
CREATE TABLE S_USERROLE (
  ID VARCHAR2(50),
  ROLENAME VARCHAR2(30),
  DEPARTMENT_ID NUMBER(8, 0)
);
CREATE  INDEX PK_S_USERROLEINDEX ON S_USERROLE (ID, ROLENAME);

DROP TABLE S_ROLE;
CREATE TABLE S_ROLE (
	ROLE VARCHAR2(50)
);
CREATE  INDEX PK_S_ROLEINDEX ON S_ROLE (ROLE);

DROP TABLE S_DICTIONARY;
CREATE TABLE S_DICTIONARY (
	DICTIONARY_KEY VARCHAR2(50),
	DEFAULT_VALUE VARCHAR2(200)
);
CREATE INDEX PK_S_DICTIONARYINDEX ON S_DICTIONARY (DICTIONARY_KEY);

CREATE TABLE S_config (
	key_1 VARCHAR(20),
	value_1 VARCHAR(2000)
);

CREATE TABLE S_news (
	key VARCHAR(50),
	title VARCHAR(100),
	content VARCHAR(2000),
	date DATE
);

DROP TABLE S_DICTIONARY_VALUE;
CREATE TABLE S_DICTIONARY_VALUE (
	DICTIONARY_KEY VARCHAR2(50),
	VALUE_KEY VARCHAR2(10),
	VALUE VARCHAR2(200)
);
CREATE INDEX PK_S_DICTIONARY_VALUEINDEX ON S_DICTIONARY_VALUE (DICTIONARY_KEY, VALUE_KEY);

CREATE TABLE S_bed (
	key_1 VARCHAR(50),
	name VARCHAR(100),
	status VARCHAR(50),
	wardKey VARCHAR(50)
);

CREATE TABLE S_building (
	key_1 VARCHAR(50),
	name VARCHAR(100),
	floor_count Number(3, 0)
);

CREATE TABLE S_ward (
	key_1 VARCHAR(50),
	name VARCHAR(100),
	building_key VARCHAR(50),
	floor Number(3, 0),
	department_seq Number(3, 0),
	beds VARCHAR(2000),
	conditions VARCHAR(100),
	extra_info VARCHAR(1000)
);

CREATE TABLE S_Hospitalization (
	key_1 VARCHAR(50),
	patient_seq Number(8, 0),
	in_date Date,
	out_date Date,
	status Number(3, 0),
	bed_key VARCHAR(50),
	addmission_type VARCHAR(50),
	extra_info VARCHAR(1000)
);

DELETE FROM S_USERS;
INSERT INTO S_USERS ( seq, id, password, name, gender, department_id, 
  degree, university, work_type, work_domain, introduction, birth_date, join_hospital_date, join_work_date )
	VALUES (seq_doctor.nextVal, 'admin','5f4dcc3b5aa765d61d8327deb882cf99','Administrator', 1, -1, '1', '1', 1, '1', '1',  to_date('2013-1-1', 'yyyy-mm-dd'),  to_date('2013-1-1', 'yyyy-mm-dd'), 
	to_date('2013-1-1', 'yyyy-mm-dd'));

DELETE FROM S_USERROLE;
INSERT INTO S_USERROLE ( ID, ROLENAME ) VALUES ('admin','admin');

DELETE FROM S_ROLE;
insert into s_role (role) values ('department');
insert into s_role (role) values ('admin');
insert into s_role (role) values ('treatment_record');
insert into s_role (role) values ('patient');




